package common.main.cnkiJournalPaper;

import common.main.common.BaseSaveDataToSql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


public class CnkiJournalPaperSaveDataToSql extends BaseSaveDataToSql {



    public static String getAllMd5(){
        try{
            connectDatabase();
            String sql = "select md5 from CnkiJournalPaper";
            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("md5") + ",");
            }
            if(sb == null){
                return "";
            }
            return sb.toString();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void saveCnkiJournalPaperToSql(List<CnkiJournalPaperData> paperList){
        try{
            connectDatabase();

            String sql_model = "INSERT INTO CnkiJournalPaper (title,author,affiliation,journal,cite_num,download_num,brief," +
                    "fund,keywords,class_num,page,issue,md5)" +
                    "values ('<title>','<author>','<affiliation>','<journal>','<cite_num>','<download_num>','<brief>'," +
                    "'<fund>','<keywords>','<class_num>','<page>','<issue>','<md5>')";
            for(int i = 0;i < paperList.size();i++){
                try{
                    CnkiJournalPaperData paperData = paperList.get(i);

                    paperData.setTitle(paperData.getTitle().replace("'","\\'"));
                    paperData.setAuthor(paperData.getAuthor().replace("'","\\'"));
                    paperData.setAffiliation(paperData.getAffiliation().replace("'","\\'"));
                    paperData.setJournal(paperData.getJournal().replace("'","\\'"));
                    paperData.setBrief(paperData.getBrief().replace("'","\\'"));
                    paperData.setFund(paperData.getFund().replace("'","\\'").replace("\n" +
                            "                  ",""));
                    paperData.setKeywords(paperData.getKeywords().replace("'","\\'").replace("\n" +
                            "                  ",""));

                    String sql = sql_model.replace("<title>",paperData.getTitle())
                            .replace("<author>",paperData.getAuthor())
                            .replace("<affiliation>",paperData.getAffiliation())
                            .replace("<journal>",paperData.getJournal())
                            .replace("<cite_num>",paperData.getCite_num())
                            .replace("<download_num>",paperData.getDownload_num())
                            .replace("<brief>",paperData.getBrief())
                            .replace("<fund>",paperData.getFund())
                            .replace("<keywords>",paperData.getKeywords())
                            .replace("<class_num>",paperData.getClass_num())
                            .replace("<page>",paperData.getPage())
                            .replace("<issue>",paperData.getIssue())
                            .replace("<md5>",paperData.getMd5());

                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
